/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import bean.Autor;
import bean.DisciplinasGS;
import bean.PDF;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author fernando
 */
public class DisciplinaDao implements Serializable {

    private Conexao conexao;
    private PreparedStatement ps;
    private boolean sucess = false;

    public DisciplinaDao() {
        this.conexao = new ConexaoPostgres();
    }

    public boolean cadastrar(DisciplinasGS disciplina) {
        sucess = false;

        try {

            ps = conexao.getConnection().prepareStatement("INSERT INTO disciplina(nome, carga_horaria, id_curso, metodologia, bibligrafia, pdf, observacao) VALUES (?, ?, ?, ?, ?, ?, ?);");
            ps.setString(1, disciplina.getNome());
            ps.setInt(2, disciplina.getCargaHoraria());
            ps.setInt(3, disciplina.getCurso().getId());
            ps.setString(4, disciplina.getMetodologia());
            ps.setString(5, disciplina.getBibliografiaPrincipal());
            if (disciplina.getPlanoDeCurso().getConteudo() != null) {
                int idPdf = new PDFDao().cadastrarPDF(disciplina.getPlanoDeCurso());
                ps.setInt(6, idPdf);
            } else {
                ps.setNull(6, Types.NULL);
            }
            ps.setString(7, disciplina.getObservacoes());
            ps.executeUpdate();
            conexao.closeConnection();

            disciplina.setId(this.buscarId(disciplina));
            Iterator i;
            if (!disciplina.getProfessores().isEmpty()) {
                i = disciplina.getProfessores().iterator();
                while (i.hasNext()) {
                    Autor prof = (Autor) i.next();
                    ps = conexao.getConnection().prepareStatement("INSERT INTO disciplina_professor(id_disciplina, id_professor) VALUES (?, ?)");
                    ps.setInt(1, disciplina.getId());
                    ps.setInt(2, prof.getId());

                    ps.executeUpdate();
                    conexao.closeConnection();

                }


            }





            sucess = true;
        } catch (SQLException ex) {
            conexao.closeConnection();
            Logger.getLogger(InstituicaoDao.class.getName()).log(Level.SEVERE, null, ex);
            return sucess;
        }

        conexao.closeConnection();
        return sucess;
    }

    public int buscarId(DisciplinasGS disciplina) {
        int id = 0;
        try {

            ps = conexao.getConnection().prepareStatement("SELECT id FROM disciplina WHERE nome = ? AND carga_horaria = ? AND id_curso = ? AND metodologia = ? AND bibligrafia = ? AND observacao = ? ");
            ps.setString(1, disciplina.getNome());
            ps.setInt(2, disciplina.getCargaHoraria());
            ps.setInt(3, disciplina.getCurso().getId());
            ps.setString(4, disciplina.getMetodologia());
            ps.setString(5, disciplina.getBibliografiaPrincipal());
            ps.setString(6, disciplina.getObservacoes());
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();
            while (rs.next()) {
                id = rs.getInt("id");

            }
        } catch (SQLException ex) {
            conexao.closeConnection();
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return id;
    }

    public int idPdfAntigo(int id) {
        int idAntigo = 0;
        try {
            ps = conexao.getConnection().prepareStatement("SELECT pdf FROM disciplina WHERE id = ? ");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();
            if (rs.next()) {

                idAntigo = rs.getInt("pdf");

            }

        } catch (SQLException ex) {
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }



        return idAntigo;
    }

    public boolean alterar(DisciplinasGS disciplina) {
        boolean resultado = false;

        try {
            ps = conexao.getConnection().prepareStatement("UPDATE disciplina SET nome=?, carga_horaria=?, id_curso=?, metodologia=?, bibligrafia=?, pdf=?, observacao=? WHERE id = ?  ");
            ps.setString(1, disciplina.getNome());
            ps.setInt(2, disciplina.getCargaHoraria());
            ps.setInt(3, disciplina.getCurso().getId());
            ps.setString(4, disciplina.getMetodologia());
            ps.setString(5, disciplina.getBibliografiaPrincipal());
            if (disciplina.getId() == 0 && !disciplina.getPlanoDeCurso().getNome().isEmpty()) {
                int idPdfAntigo = this.idPdfAntigo(disciplina.getId());
                if (idPdfAntigo > 0) {
                    new PDFDao().removerPDF(idPdfAntigo);
                }
                int idPdf = new PDFDao().cadastrarPDF(disciplina.getPlanoDeCurso());
                ps.setInt(6, idPdf);
            } else if (disciplina.getPlanoDeCurso().getId() != 0) {
                ps.setInt(6, disciplina.getPlanoDeCurso().getId());
            } else {
                ps.setNull(6, Types.NULL);

            }
            ps.setString(7, disciplina.getObservacoes());
            ps.executeUpdate();
            conexao.closeConnection();

            ps = conexao.getConnection().prepareStatement("DELETE FROM disciplina_professor WHERE id_disciplina = ? ");
            ps.setInt(1, disciplina.getId());
            ps.executeUpdate();
            conexao.closeConnection();

            Iterator i;
            if (!disciplina.getProfessores().isEmpty()) {
                i = disciplina.getProfessores().iterator();
                while (i.hasNext()) {
                    Autor prof = (Autor) i.next();
                    ps = conexao.getConnection().prepareStatement("INSERT INTO disciplina_professor(id_disciplina, id_professor) VALUES (?, ?)");
                    ps.setInt(1, disciplina.getId());
                    ps.setInt(2, prof.getId());

                    ps.executeUpdate();
                    conexao.closeConnection();

                }


            }


            resultado = true;
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return resultado;
    }

    public ArrayList<DisciplinasGS> listar() {
        ArrayList<DisciplinasGS> disciplina = new ArrayList<DisciplinasGS>();

        try {

            ps = conexao.getConnection().prepareStatement("SELECT nome, id_curso, id FROM disciplina;");
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();
            while (rs.next()) {
                DisciplinasGS d = new DisciplinasGS();
                d.setId(rs.getInt("id"));
                d.setNome((rs.getString("nome")));
                d.setCurso(new CursoDAO().buscarCursoSimplificado(rs.getInt("id_curso")));
                disciplina.add(d);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


        return disciplina;

    }

    public ArrayList<Autor> buscarProfessores(int id) {
        ArrayList<Autor> professores = new ArrayList<Autor>();
        try {
            ps = conexao.getConnection().prepareStatement("SELECT id_professor FROM disciplina_professor WHERE id_disciplina = ? ");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            AutorDao ad = new AutorDao();
            while (rs.next()) {
                Autor professor = ad.consultaAutor(rs.getInt("id_professor"));
                professores.add(professor);
            }

        } catch (SQLException ex) {
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


        return professores;
    }

    public DisciplinasGS buscar(int id) {
        DisciplinasGS disciplina = new DisciplinasGS();

        try {

            ps = conexao.getConnection().prepareStatement("SELECT nome, carga_horaria, id_curso, metodologia, bibligrafia, pdf, observacao FROM disciplina WHERE id = ?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            conexao.closeConnection();
            while (rs.next()) {

                disciplina.setId(id);
                disciplina.setNome((rs.getString("nome")));
                disciplina.setCurso(new CursoDAO().buscarCurso(rs.getInt("id_curso")));
                disciplina.setMetodologia(rs.getString("metodologia"));
                disciplina.setBibliografiaPrincipal(rs.getString("metodologia"));
                PDF pdf = new PDF();
                pdf.setId(rs.getInt("pdf"));
                disciplina.setPlanoDeCurso(pdf);
                disciplina.setObservacoes(rs.getString("observacao"));
                disciplina.setProfessores(this.buscarProfessores(id));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CursoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


        return disciplina;

    }
}
